﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using SaaS;
public partial class AddData : System.Web.UI.Page
{
    string dataComment;
    protected void Page_Load(object sender, EventArgs e)
    {
        Label_attributes.Text = "";
        if (Session.Count != 0 & DropDownList_Object.Items.Count == 0)
        {
            string strConnection = "workstation id=MiniSaaS.mssql.somee.com;packet size=4096;user id=SaaS_cse564;pwd=123abccse;data source=MiniSaaS.mssql.somee.com;persist security info=False;initial catalog=MiniSaaS";
            System.Data.SqlClient.SqlConnection Connection = new System.Data.SqlClient.SqlConnection(strConnection);
            string loginID = Session["LoginID"].ToString();
            String strSQL = "SELECT DISTINCT Table_ID FROM META WHERE Login_ID = " + loginID + ";";
            System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(strSQL, Connection);
            System.Data.SqlClient.SqlDataReader Dr;
            Connection.Open();
            Dr = command.ExecuteReader();
            System.Collections.Generic.List<int> tableIDs = new System.Collections.Generic.List<int>();
            while (Dr.Read())
                tableIDs.Add((int)Dr["Table_ID"]);
            Dr.Close();
            for (int i = 0; i < tableIDs.Count; i++)
            {
                strSQL = "SELECT Table_Name FROM META WHERE Table_ID = " + tableIDs[i].ToString() + " AND Table_Version = (SELECT MAX(Table_Version) FROM META WHERE Table_ID = " + tableIDs[i].ToString()+")";
                command = new System.Data.SqlClient.SqlCommand(strSQL, Connection);
                Dr = command.ExecuteReader();
                Dr.Read();
                DropDownList_Object.Items.Add(Dr["Table_Name"].ToString());
                Session["TableID" + i.ToString()] = tableIDs[i].ToString();
                Dr.Close();
            }
            Connection.Close();
            Session["KeysAdded"] = 0;
            DropDownList_Object_SelectedIndexChanged(null, null);
        }
    }
    protected void DropDownList_Object_SelectedIndexChanged(object sender, EventArgs e)
    {
        string selectVal = DropDownList_Object.SelectedValue;
        string TableName = selectVal;
        string tableID = Session["TableID" + DropDownList_Object.SelectedIndex.ToString()].ToString();
        int tableVer;

        string strConnection = "workstation id=MiniSaaS.mssql.somee.com;packet size=4096;user id=SaaS_cse564;pwd=123abccse;data source=MiniSaaS.mssql.somee.com;persist security info=False;initial catalog=MiniSaaS";
        System.Data.SqlClient.SqlConnection Connection = new System.Data.SqlClient.SqlConnection(strConnection);
        string loginID = Session["LoginID"].ToString();
        String strSQL = "SELECT MAX(Table_Version) FROM META WHERE Table_ID = " + tableID;
        System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(strSQL, Connection);
        System.Data.SqlClient.SqlDataReader Dr;
        Connection.Open();
        Dr = command.ExecuteReader();
        if (!Dr.HasRows)
        {
            //error - could not find reference to the Table_ID in database
            return;
        }
        Dr.Read();
        tableVer = (int)Dr[0];
        Dr.Close();
        strSQL = "Select Table_ID, Table_Columns From META WHERE Login_ID = " + loginID + "and Table_ID = "+tableID +" and Table_Version = " + tableVer.ToString() + ";";
        command = new System.Data.SqlClient.SqlCommand(strSQL, Connection);
        Dr = command.ExecuteReader();
        Dr.Read();
        string TableColumns = Dr["Table_Columns"].ToString();
        Session["xml"] = TableColumns;
        Session["TableID"] = Dr["Table_ID"].ToString();
        Dr.Close();
        XmlDocument xd = new XmlDocument();
        xd.LoadXml(TableColumns);
        XmlNodeList xnl = xd.SelectNodes("//col");

        XmlDocument xmldata = new XmlDocument();
        xmldata.LoadXml("<data></data>");
        DropDownList_Attributes.Items.Clear();
        bool first = true;
        foreach (XmlNode xn in xnl)
        {
            if (first)
            {
                //save the column ID of the first entered item
                if (xn.ChildNodes.Count >= 5)
                    //use the column ID
                    Session["ColumnID"] = xn.ChildNodes[4].InnerText.ToString();
                else
                    Session["ColumnID"] = "";
                first = false;
            }
            string nameT = xn.ChildNodes[0].InnerText + "(" +
                xn.ChildNodes[1].InnerText + ")";
            DropDownList_Attributes.Items.Add(nameT);

            XmlElement col = xmldata.CreateElement("col");
            XmlElement xmlD = xmldata.CreateElement("coldata");
            XmlElement xmlCID = xmldata.CreateElement("columnID");
            xmlD.InnerText = xn.ChildNodes[3].InnerText;
            col.AppendChild(xmlD);
            col.AppendChild(xmlCID);

            xmldata.DocumentElement.AppendChild(col);
        }
        xmldata.Save(Server.MapPath(@"App_Data\tempdata.xml"));
        DropDownList_Attributes_SelectedIndexChanged(null, null);
    }
    protected void BT_ADD_Data_Click(object sender, EventArgs e)
    {
        Label_attributes.Text = "";
        lblErr.Text = "";
        int selectVal = DropDownList_Attributes.SelectedIndex;
        //////////////////////////////////////////////////////////////////////////
        // check the type
        string selectValue = DropDownList_Attributes.SelectedValue;
        string[] temp = selectValue.Split('(', ')');
        bool flag = true;
        switch (temp[1])
        {
            case "int":
                int i = 0;
                flag = int.TryParse(this.TB_attrValue.Text, out i);
                break;
            case "double":
                double j = 0.0;
                flag = Double.TryParse(this.TB_attrValue.Text, out j);
                break;
            case "bool":
                bool k = false;
                flag = bool.TryParse(this.TB_attrValue.Text, out k);
                break;
            default:
                break;
        }
        if (flag == false)
        {
            Label_attributes.Text = "Please input the right type";
            return;
        }
        //////////////////////////////////////////////////////////////////////////
        XmlDocument xmldata = new XmlDocument();
        xmldata.Load(Server.MapPath(@"App_Data\tempdata.xml"));
        xmldata.DocumentElement.ChildNodes[selectVal].FirstChild.InnerText = this.TB_attrValue.Text;
        if (Session["ColumnID"] != null && Session["ColumnID"].ToString().Length > 0)
            xmldata.DocumentElement.ChildNodes[selectVal].ChildNodes[1].InnerText = Session["ColumnID"].ToString();
        xmldata.Save(Server.MapPath(@"App_Data\tempdata.xml"));
        
        if (Session["isPriKey"].ToString().Equals("True", StringComparison.CurrentCultureIgnoreCase))
        {
            //a value was added that was a primary key
            int keysAdded = (int)Session["KeysAdded"];
            Session["KeyCol" + keysAdded.ToString()] = Session["ColumnID"].ToString();
            Session["KeyVal" + keysAdded.ToString()] = TB_attrValue.Text;
            keysAdded++;
            Session["KeysAdded"] = keysAdded;
        }
        TB_attrValue.Text = "";
    }
    protected void DropDownList_Attributes_SelectedIndexChanged(object sender, EventArgs e)
    {
        int selectVal = DropDownList_Attributes.SelectedIndex;
        XmlDocument xd = new XmlDocument();
        xd.LoadXml(Session["xml"].ToString());
        XmlNodeList xnl = xd.SelectNodes("//col");
        string devalue = xnl[selectVal].ChildNodes[3].InnerText;
        if (xnl[selectVal].ChildNodes.Count >= 5)
        {
            //use the column ID
            XmlNode colNode = xnl[selectVal].ChildNodes[4];
            Session["ColumnID"] = colNode.InnerText.ToString();
            XmlNode priKeyNode = colNode.NextSibling;
            if (priKeyNode != null)
                Session["isPriKey"] = priKeyNode.InnerText.ToString();
            else
                Session["isPriKey"] = "False";
        }
        else
        {
            Session["ColumnID"] = "";
            Session["isPriKey"] = "False";
        }

        this.TB_attrValue.Text = devalue;
    }

    protected void clearTempData()
    {
        DropDownList_Object_SelectedIndexChanged(null, null);
        Session["KeysAdded"] = 0;
    }

    protected void FINISH_Data_Click(object sender, EventArgs e)
    {
        Label_attributes.Text = "";
        lblErr.Text = "";
        XmlDocument xmldata = new XmlDocument();
        xmldata.Load(Server.MapPath(@"App_Data\tempdata.xml"));
        dataComment = this.TB_dataComments.Text;
        String datString = xmldata.OuterXml;
        int dataID;
        int ver;
        //open database connection
        MiniSaaSDB db = new MiniSaaSDB();
        db.open();
        if (db.isOpen())
        {
            String query;
            int keysAdded = (int)Session["KeysAdded"];
            System.Data.SqlClient.SqlDataReader reader;

            if (keysAdded > 0)
            {
                //check primary key constraints
                bool duplicate = true;
                for (int i = 0; i < keysAdded; i++)
                {
                    query = "SELECT * FROM KEYS k INNER JOIN DATA d ON k.Data_ID = d.Data_ID " +
                    "WHERE d.Table_ID = " + Session["TableID"].ToString();
                    query += " AND k.Column_ID = " + Session["KeyCol" + i.ToString()].ToString() +
                        " AND k.Value = '" + Session["KeyVal" + i.ToString()].ToString() + "'";
                    reader = db.executeQuery(query);
                    if (reader != null && !reader.HasRows)
                    {
                        //a distinct row is here!
                        duplicate = false;
                        break;
                    }
                }
                if (duplicate)
                {
                    clearTempData();
                    lblErr.Text = "Duplicate primary key found - Could not add data";
                    return;
                }
            }

            int rows;
            reader = db.executeQuery("SELECT MAX(Table_Version) FROM META WHERE Table_ID = " + Session["TableID"].ToString());
            if (!reader.HasRows)
            {
                //Error - table does not exist
                lblErr.Text = "ERROR - Table does not exist";
                db.close();
                return;
            }
            else
            {
                reader.Read();
                ver = (int)reader[0];
            }
            reader.Close();
            reader = db.executeQuery("SELECT MAX(Data_ID)+1 FROM DATA");
            if (!reader.HasRows)
            {
                dataID = 1;
            }
            else
            {
                reader.Read();
                dataID = (int)reader[0];
            }
            reader.Close();
            query = "INSERT INTO DATA (Data_ID, Table_ID, Table_Version, Data_Rows, Data_Comment) " +
                "VALUES (" + dataID.ToString() + ", " + Session["TableID"].ToString() + ", " + ver.ToString() + ", '" +
                datString + "', ";
            if (dataComment != null && dataComment.Length > 0)
                query = query + "'" + dataComment + "');";
            else
                query = query + "NULL);";
            rows = db.executeNonQuery(query);
            if (rows <= 0)
            {
                //No rows affected
                lblErr.Text = "ERROR - No rows affected.";
                db.close();
                return;
            }

            if (keysAdded > 0)
            {
                //Keys were added; add data to KEYS table
                for (int i = 0; i < keysAdded; i++)
                {
                    query = "INSERT INTO KEYS (Data_ID, Column_ID, Value) VALUES (" + dataID.ToString() +
                        ", " + Session["KeyCol" + i.ToString()].ToString() + ", '" + Session["KeyVal" + i.ToString()].ToString() +
                        "');";
                    rows = db.executeNonQuery(query);
                }
            }
            clearTempData();
            db.close();
        }
        else
        {
            //Error opening DB
            lblErr.Text = "ERROR - Could not reach database";
            return;
        }
    }
}